import base64
from datetime import datetime, timedelta

from fastapi import APIRouter
from matplotlib.pyplot import xticks
from db.db_work import MysqlLite
from typing import Union
from func import data as fun_data
from matplotlib import pyplot as plt
from io import BytesIO
from func import data as data_
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams["font.family"] = ["SimHei", "WenQuanYi Micro Hei", "Heiti TC"]
plt.rcParams['axes.unicode_minus']=False

router = APIRouter()

#按照要求返回数据 mode=1,返回销售额，mode=2返回销售量，3返回销售人数
@router.get('/get_data')
def get_data(mode:int,date:Union[str,None]=None,seller_name:str=None,shop_name:str=None,user_name:str=None):
    mysql = MysqlLite()
    mysql.operation_sql("""CREATE TABLE IF NOT EXISTS history_buy (
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        dated_at TEXT DEFAULT CURRENT_TIMESTAMP,
        sum_ INTEGER,
        quantity INTEGER,
        shop_id INTEGER,
        user_id INTEGER,
        FOREIGN KEY (shop_id) REFERENCES shops(ID) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES user_info(ID) ON DELETE CASCADE
    );""")
    data = fun_data.get_date_sql()
    if date:
        filtered_df = data[data['dated_at'] >= date].reset_index(drop=True)
    else:
        if user_name:
            result=mysql.select_sql("select ID from user_info where username=?",(user_name,))[0][0]
            filtered_df=data[data['user_id']==result].reset_index(drop=True)
        else:
            result=mysql.select_sql("select ID from shops where shop_name=? and sellername=?",(shop_name,seller_name))[0][0]
            filtered_df=data[data['shop_id']==result].reset_index(drop=True)
    if mode==1:
        sum=0
        for i in range(len(filtered_df)):
            shop_id=int(filtered_df['shop_id'][i])
            price=mysql.select_sql("select price from shops where ID=?",(shop_id,))[0][0]
            sum+=price*int(filtered_df['quantity'][i])
        return {
            'code' : 200,
            'msg'  : sum
        }
    elif mode==2:
        sum=int(filtered_df['quantity'].sum())
        return{
            'code': 200,
            'msg': sum
        }
    else:
        sum=int(filtered_df['quantity'].shape[0])
        return{
            'code': 200,
            'msg': sum
        }
#根据history_buy生成可视化图片，参数mode: 1为查找最高3个用户价值，2为查找商品价值(柱状图)
@router.get('/get_value')
def get_value(mode: int):
    df=fun_data.get_date_sql()
    if mode ==1:
        group='user_id'
        title='消费最高的3个用户'
        x_text='用户名'
        msg='历史销售金额最高的三个用户可视化结果'
    else:
        group='shop_id'
        title='销售额最高的三个商品'
        x_text = '商品名与id'
        msg = '历史销售额最高的三个商品可视化结果'
    value=df['sum_'].groupby(df[group]).sum()
    value.sort_values(ascending=False, inplace=True)
    value = value.head(3)
    index = value.index.tolist()
    name=[]
    mysql=MysqlLite()
    for i in index:
        if mode==1:
            name1=mysql.select_sql("select username from user_info where ID=?",(i,))[0][0]
            name.append(name1)
        else:
            name1=mysql.select_sql("select shop_name from shops where ID=?",(i,))[0][0]
            name.append(f'{i}:{name1}')
    x = [i + 1 for i in range(len(name))]
    fig = plt.figure(figsize=(6, 5))
    bars = plt.bar(x, value, color='r')
    count = 0
    for bar in bars:
        height = bar.get_height()
        plt.text(bar.get_x() + bar.get_width() / 2., height,
                 f'金额:{int(height)}',
                 ha='center', va='bottom')
        count += 1
    xticks(x,name)
    plt.ylabel('消费总金额')
    plt.xlabel(x_text)
    plt.title(title)
    bytes = BytesIO()
    plt.savefig(bytes, format='png')
    bytes.seek(0)
    plt_img = bytes.read()
    img = base64.b64encode(plt_img).decode('utf-8')
    return {
        'code': 200,
        'msg': msg,
        'img': img
    }
#查找用户最近消费与消费总金额或消费记录，或累计各商品销售记录,result_mode为返回内容模式，1返回最近消费日期，消费总额，购买总额，2返回消费记录，3返回其累计各商品销售
@router.get('/get_user_sum_and_data')
def get_user_sum_and_data(user_id:int,result_mode :int):
    mysqlite=MysqlLite()
    sum=0
    count=0
    if result_mode==1:
        data=mysqlite.select_sql(f"select * from history_buy where user_id=? limit ?",(user_id,1))
        if data:
            if result_mode == 1:
                date = data[0][1]
                tem = mysqlite.select_sql(f"select quantity,shop_id from history_buy where user_id=?", (user_id,))
                for i in range(len(tem)):
                    price = mysqlite.select_sql(f"select price from shops where ID=?", (tem[i][1],))[0][0]
                    sum = sum + price * tem[i][0]
                    count += tem[i][0]
                return {
                    'code': 200,
                    'date': date,
                    'sum': sum,
                    'count': count,
                }
        else:
            return {
                'code': 200,
                'date': '尚未消费过哦',
                'sum': 0
            }
    elif result_mode==2:
        result=data_.get_finish_orders(user_id=user_id)
        return {
            'code': 200,
            'history_lis': result
        }
    else:
        result=[]
        shop_name_and_id=mysqlite.select_sql("select ID,shop_name,price from shops where user_id=?",(user_id,))
        for i in range(len(shop_name_and_id)):
            shop_id = shop_name_and_id[i][0]
            shop_name = shop_name_and_id[i][1]
            quantity=mysqlite.select_sql(f"select quantity from history_buy where shop_id=?", (shop_id,))
            count=0
            for j in range(len(quantity)):
                count+=quantity[j][0]
            result.append([shop_id, shop_name,count])
        return {
            'code': 200,
            'history_lis': result
        }
#获取销售额与购买额的比例
@router.get('/buy_div_seller')
def buy_div_seller(user_id:int):
    seller_sum=buy_sum=0
    mysqlite=MysqlLite()
    lis1=mysqlite.select_sql("select ID,price from shops where user_id=?",(user_id,))
    shops_id = []
    price_list = []
    for i in lis1:
        shops_id.append(i[0])
        price_list.append(i[1])
    for i in range(len(shops_id)):
        quantity=mysqlite.select_sql(f"select quantity from history_buy where shop_id=?",(shops_id[i],))
        if quantity:
            for j in range(len(quantity)):
                seller_sum=seller_sum+quantity[j][0]*price_list[i]
    lis2=mysqlite.select_sql("select quantity,shop_id from history_buy where user_id=?",(user_id,))
    quantity_lis=[]
    shops_id = []
    for i in lis2:
        quantity_lis.append(i[0])
        shops_id.append(i[1])
    for i in range(len(shops_id)):
        price=mysqlite.select_sql(f"select price from shops where ID=?",(shops_id[i],))[0][0]
        buy_sum=buy_sum+price*quantity_lis[i]
    labels=[f'销售总额',f'购买总额']
    fig=plt.figure(figsize=(5,5))
    if seller_sum==0 and buy_sum==0:
        return {
            'code': 200,
            'msg': '用户目前没有任何记录',
            'img': None,
            'seller_sum': seller_sum,
            'buy_sum': buy_sum
        }
    plt.pie([seller_sum,buy_sum],labels=labels,autopct='%1.1f%%')
    plt.legend(labels=labels)
    plt.axis('equal')
    bytes = BytesIO()
    plt.savefig(bytes, format='png')
    bytes.seek(0)
    plt_img = bytes.read()
    img = base64.b64encode(plt_img).decode('utf-8')
    return {
        'code': 200,
        'msg': 'ok',
        'img': img,
        'seller_sum': seller_sum,
        'buy_sum': buy_sum
    }
#生成近nums天mo商品销量和销售额折线图
@router.get('/shops_for_some_day')
def sales_for_some_day(nums:int,shop_id:int):
    now=datetime.now()
    end=now-timedelta(days=nums)
    days=[(end+timedelta(days=i)).strftime('%Y-%m-%d') for i in range(nums)]
    days.append(now.strftime('%Y-%m-%d'))
    end = end.strftime('%Y-%m-%d %H:%M:%S')
    mysqlite = MysqlLite()
    lis=mysqlite.select_sql("select * from history_buy where shop_id=? and dated_at>=?", (shop_id,end))
    dic1={}
    dic2={}
    for i in days:
        dic1[i]=0
        dic2[i]=0
    for tem in lis:
        dic1[tem[1].split()[0]]+=tem[5]
        dic2[tem[1].split()[0]]+=tem[2]
    fig,ax1 =plt.subplots(figsize=(10, 6))
    ax1.plot(dic1.keys(),dic1.values(),label='销售额',color='r')
    plt.title(f'id{shop_id}最近{nums}天销量与销售额')
    ax1.set_xlabel('日期')
    ax1.set_ylabel('销售额')
    plt.xticks(rotation=45)
    ax2=ax1.twinx()
    ax2.plot(dic2.keys(),dic2.values(),label='销量',color='b')
    ax2.set_ylabel('销量')
    fig.legend()
    plt.tight_layout()
    bytes = BytesIO()
    plt.savefig(bytes, format='png')
    bytes.seek(0)
    plt_img = bytes.read()
    img = base64.b64encode(plt_img).decode('utf-8')
    return {
        'code': 200,
        'msg': 'success',
        'img': img
    }
#获取mo天mo商品的销售额
@router.get('/sales_for_days')
def sales_for_days(shop_id:int,data:str):
    data=data.split(' ')[0]
    sum=data_.get_sales_for_days(shop_id,data)
    return {
        'code': 200,
        'msg': sum,
    }
#获取商品评分
@router.get('/get_score')
def get_score(shop_id:int):
    score=data_.get_score(shop_id)
    return {
        'code': 200,
        'msg': score,
    }
#获取mo商品总销售额
@router.get('/sum_price')
def buy_score(shop_id:int):
    sum=data_.get_Sales(shop_id)
    return {
        'code': 200,
        'msg': sum,
    }
#生成mo商品各总评论占比饼状图
@router.get('/get_view_score')
def get_view_score(shop_id:int):
    lis1=['🤬🤬答辩玩意','😠😠不愿多说','😑😑马马虎虎','😗😗还不错哟','😍😍十分喜欢']
    lis2=[data_.get_pl(shop_id,1),data_.get_pl(shop_id,2),data_.get_pl(shop_id,3),data_.get_pl(shop_id,4),data_.get_pl(shop_id,5)]
    if sum(lis2) ==0:
        return {
            'code': 400,
            'msg' : '尚未评论'
        }
    else:
        fig=plt.figure(figsize=(8,6))
        plt.pie(lis2,autopct='%1.1f%%')
        plt.title('各评论占比')
        plt.legend(labels=lis1)
        plt.axis('equal')
        bytes=BytesIO()
        plt.savefig(bytes, format='png')
        bytes.seek(0)
        plt_img = bytes.read()
        img = base64.b64encode(plt_img).decode('utf-8')
        return {
            'code': 200,
            'msg': 'success',
            'img': img
        }













